MySQL 中的锁是怎么回事

这篇毕竟主要以锁的范围角度来展开讲解 MySQL 中的锁

前言：MySQL 锁的学习地图

解释一下上面的图

1. 乐观锁与悲观锁是两种并发控制的思想，可用于解决丢失更新问题：

   • 乐观锁会 “乐观地” 假定大概率不会发生并发更新冲突，访问、处理数据过程中不加锁，只在更新数据时再根据版本号或时间戳判断是否有冲突，有则处理，无则提交事务；
   • 悲观锁会 “悲观地” 假定大概率会发生并发更新冲突，访问、处理数据前就加排他锁，在整个数据处理过程中锁定数据，事务提交或回滚后才释放锁；

2. InnoDB 支持多种锁粒度，默认使用行锁，锁粒度最小，锁冲突发生的概率最低，支持的并发度也最高，但系统消耗成本也相对较高；

3. 共享锁与排他锁是 InnoDB 实现的两种标准的行锁；

4. InnoDB 有三种锁算法：记录锁、gap间隙锁、还有结合了记录锁与间隙锁的 next-key锁，InnoDB 对于行的查询加锁是使用的是 next-key locking 这种算法，一定程度上解决了幻读问题；

5. 意向锁是为了支持多种粒度锁同时存在

根据范围对 MySQL 锁分类

数据库是怎样隔离事务的呢？这时候就牵连到了数据库锁。根据锁的范围 MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

MySQL 的全局锁

顾名思义，全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法

FLUSH TABLES WITH READ LOCK;

当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。

下面是一个使用全局锁的示例：

-- 获取全局锁
FLUSH TABLES WITH READ LOCK;

-- 在获取了全局锁之后，执行一些需要独占访问整个数据库的操作
-- 例如备份数据库
mysqldump -u username -p dbname > backup.sql

-- 操作完成后，释放全局锁
UNLOCK TABLES;

注意，在备份过程中整个库完全处于只读状态。

但是让整库都只读，听上去就很危险：

• 如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
• 如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟。

全表备份不加锁的问题

看来加全局锁不太好。但是细想一下，备份为什么要加锁呢？

全表备份需要加上全局锁是为了确保备份的一致性。如果在备份过程中不加全局锁，而允许其他会话对数据库进行修改，可能会导致备份数据的不一致性。

我们来看一下不加锁会有什么问题。考虑以下情况：

假设你正在进行全表备份，而同时有其他会话在修改数据库中的数据。如果没有全局锁，备份过程中数据可能会发生变化，例如有行被删除、被更新或新行被插入。这将导致备份数据与实际数据库状态不一致，备份数据可能包含已删除的行或缺少最新插入的行，破坏了备份的完整性。

以下是一个示例来说明这个问题：

假设有一个名为 users 的表，包含用户信息：

+----+----------+-----+
| id | username | age |
+----+----------+-----+
| 1  | Alice    | 25  |
| 2  | Bob      | 30  |
+----+----------+-----+

同时，有两个会话，会话 A 正在进行全表备份，而会话 B 正在执行一个修改操作。

会话 A 执行备份命令：

mysqldump -u username -p dbname > backup.sql

会话 B 执行修改命令：

UPDATE users SET age = 35 WHERE id = 1;

在会话 B 执行修改操作之后，会话 A 继续进行备份操作。然而，由于备份过程中没有加锁，备份的数据可能不一致。在最终的备份文件中，可能包含已经被会话 B 修改的数据，导致备份数据与实际数据库状态不一致。

假设在备份完成后查看备份文件 backup.sql，你可能会看到如下内容：

-- Backup of table 'users'

INSERT INTO users (id, username, age) VALUES (1, 'Alice', 35);
INSERT INTO users (id, username, age) VALUES (2, 'Bob', 30);

可以看到，备份文件中 id 为 1 的行的 age 值被修改为了 35，而不是原始的 25。这就是没有加锁导致的备份数据不一致的问题。（这个例子比较简单，如果需要同时更改两个地方，但是这里备份只同步了一处，就会出现脏数据的情况）

因此，为了确保备份数据的一致性，进行全表备份时需要加上全局锁，以防止其他会话对数据库进行修改。

提示

官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数 –single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。

你一定在疑惑，有了这个功能，为什么还需要 FTWRL 呢？一致性读是好，但前提是引擎要支持这个隔离级别。比如，对于 MyISAM 这种不支持事务的引擎，如果备份过程中有更新，总是只能取到最新的数据，那么就破坏了备份的一致性。这时，我们就需要使用 FTWRL 命令了。

所以，single-transaction 方法只适用于所有的表使用事务引擎的库。如果有的表使用了不支持事务的引擎，那么备份就只能通过 FTWRL 方法。这往往是 DBA 要求业务开发人员使用 InnoDB 替代 MyISAM 的原因之一。

MySQL 的表级锁

在MySQL中，表级锁是一种用于对数据库表进行加锁的机制。它可以限制对表的并发访问，以确保数据的完整性和一致性。

表级锁可以分为两种类型：共享锁（Shared Lock）和排他锁（Exclusive Lock）。

1. 共享锁（Shared Lock）：多个会话可以同时获取共享锁，允许并发读取数据，但不允许其他会话获取排他锁或修改数据。共享锁用于防止并发修改，确保读取数据的一致性。

2. 排他锁（Exclusive Lock）：只有一个会话可以获取排他锁，其他会话无法同时获取共享锁或排他锁。排他锁用于防止并发读取和修改，保证对数据的独占访问。

下面是一个使用表级锁的示例：

会话 A 获取共享锁：

-- 获取共享锁
LOCK TABLES users READ;

-- 执行读取操作
SELECT * FROM users;

-- 释放锁
UNLOCK TABLES;

会话 B 获取排他锁：

-- 获取排他锁
LOCK TABLES users WRITE;

-- 执行修改操作
UPDATE users SET age = 35 WHERE id = 1;

-- 释放锁
UNLOCK TABLES;

在上面的示例中，会话A通过 LOCK TABLES users READ 获取了对 users 表的共享锁，然后执行了读取操作。共享锁允许其他会话同时获取共享锁，但不允许获取排他锁或修改数据。最后，使用 UNLOCK TABLES 释放了锁。

同时，会话 B 通过 LOCK TABLES users WRITE 获取了对 users 表的排他锁，然后执行了修改操作。排他锁只允许一个会话获取，其他会话无法同时获取共享锁或排他锁。最后，使用 UNLOCK TABLES 释放了锁。

通过表级锁，可以在需要对整个表进行操作时对其进行加锁，以确保并发访问的数据一致性。然而，需要注意的是，表级锁对数据库的并发性能可能会产生一定的影响，因此在使用表级锁时需要谨慎考虑。

表锁中的元数据锁 MDL

在 MySQL 中，MDL（Metadata Locking）是一种元数据锁（其实也是表锁的一种机制），用于保护数据库对象（如表、索引、视图等）的结构和定义。它用于协调并发操作，以确保数据定义的一致性和完整性。

MDL 不需要显式使用，在 MySQL 中，MDL 锁通常是自动加上的，而无需显式地使用锁语句来获取。MySQL 的内部逻辑会根据需要自动获取和释放适当的 MDL 锁。

例如，在执行诸如表结构修改（ALTER TABLE）或表重命名（RENAME TABLE）等操作时，MySQL 会自动获取适当的MDL写锁，以确保这些操作在并发环境中的安全性和一致性。

当会话执行一个需要获取MDL锁的操作时，它将自动等待获取所需的锁。如果其他会话已经持有了相应的 MDL 锁，那么等待的会话将被阻塞，直到锁可用为止。

提示

需要注意的是，MDL 锁的自动管理是 MySQL 的内部机制，用户通常不需要显式地处理 MDL 锁。但是，在某些特殊情况下，例如需要显式锁定表或避免长时间的 MDL 锁等待时，可以使用显式的 MDL 锁语句（如 LOCK TABLES）来控制 MDL 锁的获取和释放。

因此，在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

• 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
• 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

MDL 锁导致的 DB 被打爆

虽然 MDL 锁是系统默认会加的，但却有个坑，很容易出现给一个小表加个字段，导致整个库挂了。

首先要知道 给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。

这里举个具体的导致数据库爆掉场景例子：

有以下 A,B,C,D 四个事务

我们可以看到 session A 先启动，这时候会对表 t 加一个 MDL 读锁。由于 session B 需要的也是 MDL 读锁，因此可以正常执行。

之后 session C 会被 blocked，是因为 session A 的 MDL 读锁还没有释放（因为它在扫描全表的数据），而 session C 需要 MDL 写锁，因此只能被阻塞。

如果只有 session C 自己被阻塞还没什么关系，但是之后所有要在表 t 上新申请 MDL 读锁的请求也会被 session C 阻塞。前面我们说了，所有对表的增删改查操作都需要先申请 MDL 读锁，就都被锁住，等于这个表，现在完全不可读写了。

如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新 session 再请求的话，这个库的线程很快就会爆满。

安全的给热点表加字段

若要安全地给热点表加字段，以避免连接爆满的情况，可以考虑以下步骤和建议：

1. 预估操作的影响：在执行任何结构修改操作之前，先评估该操作对数据库的影响范围和持续时间。热点表的结构修改可能会导致较长时间的锁定和阻塞，影响其他会话的访问。确保你了解操作的潜在风险，并在低峰期或计划维护窗口执行。

2. 备份数据：在进行任何结构修改之前，务必进行完整的数据备份。这样，在出现意外情况时，可以恢复到先前的状态。

3. 逐步执行修改：将结构修改操作划分为多个较小的步骤，逐步进行。每个步骤的执行时间应该短暂，并及时释放锁，以允许其他会话的访问。

4. 优化操作：确保在结构修改操作中使用有效的查询和更新语句，包括索引的使用。优化操作可以减少锁定的范围和持续时间。

5. 避免锁等待：在执行结构修改操作期间，其他会话可能会等待MDL锁的释放。尽量避免长时间持有锁，考虑在较小的事务中执行结构修改，以减少锁定的时间。

6. 定时通知和维护窗口：提前通知数据库用户或相关团队，关于将要进行结构修改操作的计划。在事先确定的维护窗口期间执行操作，这样用户可以预先了解到可能的连接问题，并做出相应的调整。

7. 监控和调整：在执行结构修改操作期间，密切监控数据库的连接数、锁等待和性能指标。根据监控结果，进行必要的调整和优化，以确保数据库在操作过程中的可用性和性能。

通过采取以上措施，可以最大程度地减少连接爆满的风险，安全地给热点表添加字段，并确保数据库的可用性和性能。

当给热点表添加字段时，以下是一个具体的操作例子，以避免连接爆满的情况：

1. 预估操作的影响：在执行之前，预估结构修改操作对数据库的影响范围和持续时间。例如，考虑表的大小、索引情况以及所需的锁定时间。

2. 备份数据：在执行结构修改之前，确保进行了完整的数据备份，以防止意外情况发生。这样可以在需要时恢复到先前的状态。

3. 拆分操作为多个步骤：将结构修改操作拆分为多个较小的步骤，逐步进行。例如，可以按以下方式执行：

   a. 创建一个新的临时表，具有新添加字段的结构。 b. 将原始表中的数据复制到临时表中。 c. 重命名原始表为备份表，并将临时表重命名为原始表。 d. 根据需要更新新表结构。

   每个步骤的执行时间应该短暂，尽快完成并释放锁，以允许其他会话的访问。

4. 优化操作：确保使用有效的查询和更新语句，包括适当的索引使用。根据表的大小和数据量，考虑使用合适的批量操作和分段执行，以减少锁定的范围和持续时间。

5. 定时通知和维护窗口：提前通知数据库用户或相关团队，关于将要进行结构修改操作的计划。在事先确定的维护窗口期间执行操作，这样用户可以预先了解到可能的连接问题，并做出相应的调整。

6. 监控和调整：在操作期间，密切监控数据库的连接数、锁等待和性能指标。使用MySQL的性能监控工具或第三方工具，实时监控数据库的状态。根据监控结果，进行必要的调整和优化，以确保数据库在操作过程中的可用性和性能。

表锁的使用场景

表级锁在MySQL中主要用于控制对整个表的并发访问和修改。下面是一些常见的使用场景，适合使用表级锁：

1. 全表操作：当需要对整个表执行操作时，如全表备份、全表恢复、表结构修改等，可以使用表级锁来防止其他会话对表进行并发操作，确保操作的一致性和完整性。

2. 数据表维护：在进行数据表维护任务时，如数据清理、数据迁移、索引重建等，可以使用表级锁来避免并发操作对维护任务的干扰。

3. 数据导入和导出：在大量数据的导入和导出过程中，为了保持数据的一致性，可以使用表级锁来防止其他会话对表进行并发修改，确保数据的完整性。

4. 数据修复和恢复：在数据库出现故障或数据损坏时，需要进行数据修复或恢复操作，可以使用表级锁来防止其他会话对受影响的表进行并发修改，以确保修复或恢复操作的准确性。

5. 特殊处理需求：在某些特殊场景下，需要对整个表进行特殊处理，如表的分割、合并或重建等，可以使用表级锁来确保操作的原子性和一致性。

表级锁在锁定表时会对其他会话的并发性能产生影响，因此在使用表级锁时需要谨慎考虑并发访问的需求和数据库的性能要求。在某些情况下，更细粒度的行级锁或其他并发控制机制可能更适合，以避免过度的锁竞争和性能问题。

MySQL 的行锁

这里只是简单的讲讲，具体的看其它的笔记

MySQL 的行锁是一种锁机制，用于控制对数据库表中单行或多行数据的并发访问和修改。行级锁允许多个会话同时读取同一表的不同行，但在写操作时会对所涉及的行进行锁定，以防止其他会话对相同行进行并发修改。

以下是一个使用行锁的示例：

假设有一个名为 products 的表，用于存储产品信息，其中包含 id、name 和 quantity 等列。

会话 A 和会话 B 同时执行如下事务：

会话A：

START TRANSACTION;
SELECT * FROM products WHERE id = 1; -- 读取产品1的信息

会话B：

START TRANSACTION;
SELECT * FROM products WHERE id = 1; -- 读取产品1的信息

在这个示例中，两个会话尝试读取相同 id 为 1 的产品信息。如果没有行级锁的存在，它们可以并发地读取相同的行。

然而，当会话 A 读取产品 1 的信息时，会自动获取对该行的行级共享锁。这将阻止其他会话在会话 A 释放锁之前修改该行。

现在，会话 B 也尝试读取产品 1 的信息。由于该行已经被会话 A 锁定，会话 B 必须等待会话 A 释放锁才能继续执行。

如果会话 A 执行如下操作后释放锁：

UPDATE products SET quantity = quantity + 1 WHERE id = 1; -- 对产品1的数量进行更新
COMMIT;

随后，会话 B 才能获取对产品 1 的行级共享锁，并读取最新的产品信息。

通过行级锁，MySQL提供了更细粒度的并发控制，允许在多个会话同时读取表的不同行，同时保护写操作的数据一致性。这样可以提高并发性能和数据的并发访问能力。

MySQL 的锁粒度

从锁的粒度的角度来看，MySQL 中的锁分为：

• 表级锁
• 行级锁
• 页面锁

表级锁：对整张表加锁。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

行级锁：对某行记录加锁。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。（注意，这个是 NDB 才有的）

在实际开发过程中，主要会使用到表级锁和行级锁两种。既然锁是针对资源的，那么这些资源就是数据，在 MySQL 提供插件式存储引擎（这里只讲 InnoDB 的因为其它的没有事务）对数据进行存储。

Reference

• MySQL 实战 45 讲